package com.lc.builder;

import com.google.gson.Gson;
import com.lc.bean.Constants;
import com.lc.bean.FieldInfo;
import com.lc.bean.TableInfo;
import com.lc.utils.PropertiesUtils;
import com.lc.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

/**
 * 功能：读取table
 * 作者：lc
 * 日期：2024/6/10 21:18
 */
public class BuildTable {
    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection conn = null;
    private static final String SQL_SHOW_TABLE_STATUS = "SHOW TABLE STATUS;";
    private static final String SQL_SHOW_TABLE_FIELDS = "SHOW FULL COLUMNS FROM %s";
    private static final String SQL_SHOW_TABLE_INDEX = "SHOW INDEX FROM %s";


    // 首先进行数据库连接
    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String user = PropertiesUtils.getString("db.user");
        String password = PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            logger.error("数据库连接失败：", e);
        }
    }

    /**
     * 读取数据库中的表
     */
    public static List<TableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet tableResult = null;
        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()) {
                String tableName = tableResult.getString("name");
                String comment = tableResult.getString("comment");

                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);

                }
                beanName = processFiled(beanName, true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);
                readFieldInfo(tableInfo);
                getKeyIndexInfo(tableInfo);
                tableInfoList.add(tableInfo);
                logger.info(new Gson().toJson(tableInfo));

            }
        } catch (Exception e) {
            logger.error("读取数据库表信息失败！", e);
        } finally {
            if (tableResult != null) {
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return tableInfoList;
    }


    public static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldsResult = null;
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        List<FieldInfo> fieldExtends = new ArrayList<>();
        try {

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));
            fieldsResult = ps.executeQuery();
            Boolean haveDate = false;
            Boolean haveDateTime = false;
            Boolean haveBigDecimal = false;
            while (fieldsResult.next()) {
                String field = fieldsResult.getString("field");
                String type = fieldsResult.getString("type");
                String extra = fieldsResult.getString("extra");
                String comment = fieldsResult.getString("comment");
                /**
                 * 是否需要前缀
                 */
                // field = field.substring(field.indexOf("_") + 1);
                String propertyName = processFiled(field, false);
                if (type.indexOf("(") > 0) {
                    type = type.substring(0, type.indexOf("("));
                }
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);
                fieldInfo.setFieldName(field);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setIsAutoIncrement("auto_increment".equals(extra));
                fieldInfo.setJavaType(processJavaType(type));
                tableInfo.setFieldList(fieldInfoList);
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
                    haveDateTime = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
                    haveBigDecimal = true;
                }
                //String类型
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPE, type)) {
                    FieldInfo fuzzyField = new FieldInfo();
                    fuzzyField.setPropertyName(propertyName+Constants.SUFFIX_TABLE_QUERY_FUZZY);
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setFieldName(fieldInfo.getFieldName());
                    fuzzyField.setSqlType(type);
                    fieldExtends.add(fuzzyField);
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES,type) ||
                        ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
                    FieldInfo startField = new FieldInfo();
                    startField.setPropertyName(fieldInfo.getPropertyName() + Constants.SUFFIX_TABLE_QUERY_START);
                    startField.setJavaType("String");
                    startField.setFieldName(fieldInfo.getFieldName());
                    startField.setSqlType(type);
                    fieldExtends.add(startField);

                    FieldInfo endField = new FieldInfo();
                    endField.setPropertyName(fieldInfo.getPropertyName() + Constants.SUFFIX_TABLE_QUERY_END);
                    endField.setJavaType("String");
                    endField.setFieldName(fieldInfo.getFieldName());
                    endField.setSqlType(type);
                    fieldExtends.add(endField);
                }
            }
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setFieldExtends(fieldExtends);

        } catch (Exception e) {
            logger.error("读取数据库表结构信息失败！", e);
        } finally {
            if (fieldsResult != null) {
                try {
                    fieldsResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }

    }

    /**
     * 读取索引
     *
     * @param tableInfo
     * @return
     */
    public static void getKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldsResult = null;
        try {
            Map<String, FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo :
                    tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(), fieldInfo);
            }

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            fieldsResult = ps.executeQuery();
            while (fieldsResult.next()) {
                Integer nonUnique = fieldsResult.getInt("non_unique");
                String keyName = fieldsResult.getString("key_name");
                String columnName = fieldsResult.getString("column_name");
                if (nonUnique != 0) {
                    continue;
                }
                logger.info("第一次打印{}", new Gson().toJson(tableInfo));
                List<FieldInfo> keyFieldList = tableInfo.
                        getKeyIndexMap().computeIfAbsent(keyName, k -> new ArrayList<>());
                keyFieldList.add(tempMap.get(columnName));
                logger.info("第二次打印{}", new Gson().toJson(tableInfo));
            }
        } catch (Exception e) {
            logger.error("表索引读取失败!", e);
        } finally {
            if (fieldsResult != null) {
                try {
                    fieldsResult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 首单词首字母大写
     * 参数true 类名  参数false
     *
     * @param field
     * @param upperCaseFirstLetter
     * @return String sb.toString()
     */
    private static String processFiled(String field, Boolean upperCaseFirstLetter) {
        StringBuffer sb = new StringBuffer();

        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1, len = fields.length; i < len; i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    /**
     * 属性类型
     *
     * @param type
     * @return String JavaType
     */
    private static String processJavaType(String type) {
        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPE, type)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPE, type)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_STRING_TYPE, type)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
            return "BigDecimal";
        } else if (ArrayUtils.contains(Constants.SQL_BIGINTEGER_TYPE, type)) {
            return "BigInteger";
        } else {
            return "String";
        }

    }
}
